﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Net.Mail;
using System.Net;
using System.Security.Cryptography;

namespace DAL
{
    public class DBConnection
    {
        public const String DATA_SOURCE = @"NGOCTRAN\SQLEXPRESS";
        public const String CATALOG = "mobilephone";

        private static SqlConnection connection;


       private static SqlCommand cmd;
       private static SqlDataAdapter da;

        private DBConnection()
        {
            // Constructor
        }

        public static SqlConnection getConnect()
        {
            
            try
            {
                String SqlConnection = "Data Source=" +DATA_SOURCE +";Initial Catalog=" +
                    CATALOG + ";Integrated Security=True";
                connection = new SqlConnection(SqlConnection);
                connection.Open();
                return connection;
               
            }
            catch (SqlException e)
            {
                connection = null;
                return connection;

            }
            catch (Exception e)
            {
                Console.Write(e.StackTrace);
                return null;
            }
            
        }


        public static DataTable getALL(String storeProcedureName)
        {
            

            try
            {
                connection = DBConnection.getConnect();
                cmd = new SqlCommand(storeProcedureName, connection);
                cmd.CommandType = CommandType.StoredProcedure;

                DataTable dt = new DataTable();
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;

            }

            catch (SqlException e)
            {
                return null;

            }
            catch (Exception e)
            {
                return null;

            }
            finally
            {
                if (da != null)
                {
                    da.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }


        public static DataTable GetDatatableByStoreProcedureName(string storeProcedureName, SqlParameter[] param)
        {
           try
            {
                connection = DBConnection.getConnect();
                cmd = new SqlCommand(storeProcedureName, connection);
                cmd.CommandType = CommandType.StoredProcedure;

                for (int i = 0; i < param.Length; i++)
                {
                    cmd.Parameters.Add(param[i]);
                }

                DataTable dt = new DataTable();
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;

            }

            catch (SqlException e)
            {
                return null;

            }
            catch (Exception e)
            {
                return null;

            }
            finally
            {
                if (da != null)
                {
                    da.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }



        public static Boolean QueryByStoreProcedureName(string storeProcedureName, SqlParameter[] param)
        {
            try
            {
                connection = DBConnection.getConnect();
                cmd = new SqlCommand(storeProcedureName, connection);
                cmd.CommandType = CommandType.StoredProcedure;

                for (int i = 0; i < param.Length; i++)
                {
                    cmd.Parameters.Add(param[i]);
                }

                int rows = cmd.ExecuteNonQuery();
                return true;


            }

            catch (SqlException e)
            {
                return false;

            }
            catch (Exception e)
            {
                return false;

            }
            finally
            {
                if (da != null)
                {
                    da.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }


        public static bool SendMail_By_Gmail(string to, string strSubject, string strContent)
        {
            MailMessage mail = new MailMessage();

            mail.To.Add(to);
            mail.Subject = strSubject;
            mail.IsBodyHtml = true;
            mail.Body = strContent;
            mail.From = new MailAddress("it.mr.bill@gmail.com");
            try
            {
                SmtpClient client = new SmtpClient();
                client.DeliveryMethod = SmtpDeliveryMethod.Network;
                client.EnableSsl = true;
                client.Host = "smtp.gmail.com";
                client.Port = 587;
                // khai báo tài khoản và mat khẩu gừi mail
                System.Net.NetworkCredential credentials = new NetworkCredential("it.mr.bill@gmail.com", "");
                client.UseDefaultCredentials = false;
                client.Credentials = credentials;
                client.Send(mail); // thuc hien gui mail
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Loi " + ex.ToString());
                return false;
            }

        }

        public static string mahoamk(string matkhau, bool ues)
        {
            byte[] keyArry;
            byte[] toEncryArry = UTF8Encoding.UTF8.GetBytes(matkhau);
            if (ues)
            {

                MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
                keyArry = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes("nhox"));

            }
            else keyArry = UTF8Encoding.UTF8.GetBytes("nhox");
            TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
            tdes.Key = keyArry;
            tdes.Mode = CipherMode.ECB;
            tdes.Padding = PaddingMode.PKCS7;
            ICryptoTransform cTranfrom = tdes.CreateEncryptor();
            byte[] kq = cTranfrom.TransformFinalBlock(toEncryArry, 0, toEncryArry.Length);
            return Convert.ToBase64String(kq, 0, kq.Length);


        }


        public static string giamamk(string matkhau, bool ues)
        {
            byte[] keyArry;
            byte[] toEncryArry = Convert.FromBase64String(matkhau);
            if (ues)
            {
                MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
                keyArry = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes("nhox"));


            }
            else

                keyArry = UTF8Encoding.UTF8.GetBytes("nhox");
            TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
            tdes.Key = keyArry;
            tdes.Mode = CipherMode.ECB;
            tdes.Padding = PaddingMode.PKCS7;
            ICryptoTransform cTranfrom = tdes.CreateEncryptor();
            byte[] kq = cTranfrom.TransformFinalBlock(toEncryArry, 0, toEncryArry.Length);
            return UTF8Encoding.UTF8.GetString(kq);


        }

        
    }
}
